Getting started

GeniusConnect Manual ver. 2.5

GeniusConnect Download...

Copyright © 2023 by GeniusConnect Team.


Welcome

Getting started ››

Welcome

GeniusConnect will add database connectivity to Microsoft Outlook. Items can be transferred between Microsoft Outlook/Exchange folder(s) and a (SQL) database.

GeniusConnect supports synchronization of the following Outlook / Exchange Folder types:

Microsoft Outlook 2002/2003/2007/2010/2013/2016/2019/2021

-Calendar

-Contact

-Mail

-Task

-Journal

-Note

Microsoft Outlook 2000

-Contact

Limitations

Exceptions on recurring appointments and tasks are not supported.

Free version Limitation

49 records max., Auto Synchronisation disabled, folder options disabled.

Configuration

Configure Folders and synchronization options.

Synchronize

After configuration has been done, use this section to read more about manual or automatic synchronization.

http://www.geniusconnect.com


Installation

Getting started ››

Download GeniusConnect installation for your Office version: https://www.geniusconnect.com/geniusconnect-downloads/

How to install GeniusConnect.

1.) First make sure Outlook is closed before installing

2.) double click the GeniusConnectXXX..exe file

3.) Follow the instructions on the screen

4.) Start Outlook

GeniusConnect Ribbon will be visible in Microsoft Outlook after installation.

Configuration

See also: Automatic Deployment


How to config

Configuration ››

Configuration

Outlook 2000/2002/2003/2007 toolbar (See also Customize Toolbar )

Outlook 2010 and higher Ribbon

The configuration [image] and the Help [image] buttons are always visible and enabled.

The other 4 buttons (Load item, Load all, Store item, Store all) are only functional when the selected/current Outlook folder is linked to a database table/view.

Ribbon ONLY

The checkbox “Show Progress” makes it possible to enable/disable progress dialog without starting the configuration dialog.

You can start the help file from the toolbar by clicking the Help [image] button.

The help file can also be started from every configuration dialog by pressing the Help button or F1.

Configuration dialog can be started by pressing the [image] button, dialog contains the following tabs:

  • Options
  • Database
  • Synchronize Schedule
  • Register
  • About & Contact Info

You can also use the Configuration steps to walk through the complete configuration.


Options

Configuration ››

Configuration – Options

Application Options

These options affect all folders.

Use progress window to show current progress during database operations
if enabled, the progress window displays all status, info, warning and error messages.

Close the progress window after synchronization has ended
after the synchronization has ended (without an error) the progress windows is closed.

Status bar will be used when progress window is not selected
this option is disabled and ignored if the progress window is used. Enable this option to see progress messages in the Outlook Status bar. In this mode it is possible to abort the synchronization by pressing the Escape key.
Please note:
If you disable this option no progress will be visible! It is also not possible to abort the synchronization.

Block error and warning Message Box
enable this option to block popup message box with error/warning text. This option will not suppress the “Yes/No” dialogs. See the Silent delete option in the Folder Options for suppressing the “Yes/No” dialogs.

Log errors and warnings to event log
errors and warnings are reported in the Application Log of the Windows event log.
Requires Windows XP or higher, Windows 2000 Server or higher.

Log Directory
Enter a directory for log files. Leave blank for no log.
All information, warning and error messages are written to the log file with timestamp value.

Log file is created during Outlook start-up (recycling on a daily basis, for log file per sync see registry setting LogFilePerSync )

Please note that using log files and progress window can affect the performance.

For best performance disable the use of progress window and leave the log directory empty. If any error occurs the error message box and progress window will automatically popup.

Log files are created in the following format:GC<DATETIME>.log

DATETIME format: Year Month Day Hour Minute Example: GC20050921233949.log

Time Out in second’s
Time limit before a query to the database times out. (Enter 0 to wait infinite).

Time out can be also modified per folder. Folder settings will always overrule default settings.
( Assign Table Dialog(Options )

Configuration Password
Create a password for all configuration options. If password has been entered, user can’t change any setting without entering the password.

Check for updates
Check for new version of GeniusConnect

Advanced admin options
see Advanced registry settings

Configuration - Database


Configuration steps

Configuration ››

Configuration steps

Follow these steps to complete configuration:

  1. Create an ODBC data source
  2. Select an Outlook folder to link a database table to
  3. Specify the database - Outlook link
  4. Make a conversion for date/time field if necessary
  5. Make a conversion for bit and long fields if necessary
  6. Specify Primary and Mandatory Keys
  7. Set Folder Options
  8. Set Filter if necessary
  9. Execute Initial sync if necessary

After completing these steps, an Outlook folder is linked to a database and the Synchronization can be started.


Register License

Configuration ››

1. Click GeniusConnect setup toolbar button

2. Click Register Tab

3 Enter the registration data EXACTLY as stated.

EXAMPLE:

Enter [MyLicenseName Lic.count=X] in the Name edit field.

(NOT the [] characters!)

4.Click Register button


Database Tab

Configuration ›› Database ››

Configuration - Database

If the folder is linked to a database table/view the ODBC data source and table/View names are shown behind the folder.

To add a table, first select a folder (Only Contacts Folders for Microsoft Outlook 2000), then click on the ‘ Assign table ’ button.

To remove a table, first select a folder which has a table connected to it, click on the ‘Remove table’ button to remove the link between the Outlook folder and the database table.

When an Outlook folder is already linked to a database table the link can be reconfigured by clicking on the ‘Assign table’ button.

Use the ‘Copy profile to…’ button to copy configuration of a selected folder to other folders of the same type.

Check the target folders and click “Yes” button.

See also dynamic folder parameters {OUTLOOK_FOLDER_XXX}. When using dynamic parameters, same profiles will result in for example a different filter or mandatory/primary key SQL statement.

More Actions button and Context menu functions

Select a folder and click More Actions button or Right Click a Folder to activate context menu functions:

Clear Primary keys
see topic: Clear primary keys from Outlook items

New Folder

Create a new folder as sub-folder of the selected folder.

Delete Folder

Delete selected folder.

Disable Sync

Keep configuration but disable all sync operations for this folder.

Check folder permission

Check if GeniusConnect/Current user has all permissions to perform sync.

If user has only read permissions, GeniusConnect will not be able to perform synchronization, only export of Folder items to Database will be possible provided that Primary key storage is to File.

Export profile

Export all settings of a selected folder to a file

Import profile

Import previously exported file to a selected folder

Check deleted folders profile

Check for profiles without an Outlook Folder (Folder has been deleted)

Import deleted folders profile

Check for profiles without an Outlook Folder and link the profile to a folder

Advanced Menu

Submit after insert | update

Mail item after insert (from database) into the folder. You can prepare mailings, meetings, tasks in database and mail them to recipients

Delete item after saved to Database

Delete item from Outlook after saved to database

Move item after saved to Database

Move item after saved to database to a folder

Delete item after saved... and Move item after saved... are mutually exclusive, selecting move to folder will disable Delete Item function and vice versa.

Slow Scan Helper

Disable scanning of a folder (and sub folders). Can be used for slow connections (hosted Exchange/Office 365)on folders that are never used in GeniusConnect.

This is a helper function, you can edit this option directly in Advanced Options dialog:

Add Exchange / Office 365 Mailbox

Add Exchange Mailbox (older MS Exchange versions and Multi-User license only)

Add Exchange Mailboxes to GeniusConnect profile

GeniusConnect will display “Select Exchange user(s)” dialog. You can select one or more users. You must have Admin permission on every Mailbox you want to add!

GeniusConnect displays shared mailbox as “Exchange Mailbox [User Name]”

Remove Exchange Mailbox (older MS Exchange versions and Multi-User license only)
Remove Exchange Mailbox from GeniusConnect profile
(Enabled only if other user mailbox selected)

For recent Exchange / Office 365 accounts please use Outlook account admin

See also:

Assign table

Configuration - Options

Create a datasource .

Initial Sync

Clear primary key from Outlook items


Assign Table Dialog

Configuration ›› Database ›› Assign table ››

Assign table

Use this dialog to create a link between an Outlook folder and a database table/view.

Steps:

  1. Select an ODBC data source
    How to create an ODBC data source is explained here .
  2. Enter a valid login name or leave blank when no login name is needed
  3. Enter a valid password or leave blank when no password is needed
  4. Hit the “Connect” button.
  5. Select the table/view in Table Combo box

After you have selected a table, available Outlook fields are listed in the list view underneath.

  6. Assign the fields
    Field information
  1. Import a template file (See Example: Microsoft SQL Server or Microsoft Access ) with mappings.

OR

  1. Manual by selecting the database fields
    Assign fields
  2. Export to a file for later use.

Do not forget to specify Primary and Mandatory keys , to do this please click the “ Mandatory Keys ” button.

Folder Options

ANSI quoted identifiers

Conversion

Filter rows

User Defined Fields

Item attachments

Unicode version only:

Use this button to activate Unicode syntax dialog.

Remark:

Each table must have at least one primary key !


Assign fields

Configuration ›› Database ›› Assign table ››

Assign fields

To link an Outlook field to a database field, click at the ‘DB Field’ column part of the Outlook field row to display a combo box with available columns. Select in the combo box the appropriate database field. Each database field can only be linked to one Outlook field.

Every Outlook Folder Type (Calendar, Contacts etc.) has a different set of available fields (depends also on Outlook Version). Please see Outlook documentation and Microsoft Support site for details about available Outlook Fields.

Picture 1: Link the database field ‘OCS_FIRSTNAME’ to the Outlook field ‘First Name’ by selecting the database field.

After linking the fields, you can specify Conversions and primary and mandatory keys.

Mandatory keys and primary keys that are not linked to an Outlook field must finally get a value by specifying a SQL statement. Please click the ‘ Mandatory' button to open the appropriate dialog.

  • Conversion
    Specify (per column) the to transform Outlook data to a table column. This is necessary if the data type of Outlook Item does not match with the data type of the table column.
    Example: Outlook date field linked to a character table column, you have to specify the date format used to store the date value in a character column.
    Conversion can be necessary also with the same data types.
    Example: Outlook High Importance is represented by value 2 but in the table column is the High Importance represented by value 3. Storing the value 3 in Outlook Item will fail without a conversion.
  • Check types
    After mapping the fields, you can click on the ‘Check Types’ button to check if all linked fields can be converted from the Outlook field type to the database field type and back. If conversion is necessary or not possible, the row will be marked by an icon at the left side of the row.

Conversion is not necessary (some types can be auto. converted).

Conversion is necessary to link the Outlook field to the database field.

No conversion is possible. This database field cannot be linked to the selected Outlook field.

  • Show database values in floating windows
    Shows a dialog which display’s the first 25 records of the table, if there are any.

It is possible to export Folder mappings and options to a definition/template file. This file can be used for another Outlook folder, or to link folders on different computers. To do this use the ‘Export’ and ‘Import’ buttons at the bottom of the ‘Assign table’ dialog.

To export/import folder including Connection and table info, see topic: Configuration - Database (Context menu)

For Auto deployment please see: Automatic Deployment

See also:

Conversion

Field information

Mandatory keys

User Defined Fields


Field information

Configuration ›› Database ›› Assign table ››

Field information

The columns in the list view are:

  1. Outlook Field
  2. Outlook Type
  3. DB Field
    can be selected from a combo box
  4. DB Type
    column data type
  5. Conversion
    can be set by using the ‘ Conversion ’ dialog
  6. Comment
    hint text

See also:

Assign table

Conversion

Mandatory keys


Primary and Mandatory keys

Configuration ›› Database ›› Assign table ››

Primary and Mandatory keys

A primary key column(s) must get a value from a SQL statement.

PRIMARY KEY constraints identify the column or set of columns whose values uniquely identify a row in a table. No two rows in a table can have the same primary key value.

Mandatory keys, which are not linked to an Outlook field and are not getting value automatically in the database (DEFAULTS etc.), must get a value from a SQL statement.

GeniusConnect will execute these SQL Statements and the result values will be used to insert a new record into the database table.

The SQL Statement can be any valid SQL Statement returning 1 single value.

The result value must be:

  • Unique for primary key columns or columns with unique index
  • Compatible with data type of the table column

For every Outlook Folder at least one primary key MUST be defined!

SQL Statement for a new Primary key will be executed only for inserting of a new record to the table.

SQL Statement for a Mandatory key will be executed (and the result value will be used) also for record updates.

Example:

GeniusConnect steps during saving a new Outlook Item into a database table.

Table: MyContactTable

Primary Key: ID

SQL Statement to generate a new ID: select max(ID)+1 from MyContactTable

Steps:

  • Execute select max(ID)+1 from MyContactTable
  • Get results, result=100
  • Execute: insert into MyContactTable (ID, All Other columns…) VALUES(100, All Other column values)

The columns in the list view are:

  1. Database field name
  2. Type (primary or mandatory)
  3. SQL statement (must be entered by the user)
  4. Identity (also Auto Increment, AutoNumber etc.)
    Yes=database automatically generates values during insert. (disabled when specifying mandatory keys for Attachments mapping )
    No=Value must be provided with the Insert statement

GeniusConnect will try to detect the primary/mandatory keys using functionality of ODBC driver.

Not every ODBC driver supports this feature. If it is not possible to detect primary/mandatory fields, the fields must be added manually by clicking the Add button.

In the first column the field can be selected from a drop down list.

In the second column the key type must be specified.

In the third column the SQL statement must be specified.

If you are using Identity columns, set the value to “Yes” and specify a SQL statement which returns the last-inserted identity value.

Example:

NOTE:
If you are using MS Access auto numbers, you do not have to set the Identity to YES!
MS Access allows to provide values for auto numbers with the Insert statement.

By clicking on the row of the database field at the ‘SQL statement’ column the user can enter the SQL statement.

You can use the “Test” button to test your SQL Statement.

If your SQL statement results in an error (or is empty),

GeniusConnect will not be able to insert new records into the database table during the real synchronization.

Example SQL Statements:

Note: SQL syntax is database dependent; these examples will not work for every database system!

Primary key

For integer fields:

Database Independent:

select max(YOUR_COLUMN_NAME) + 1 from YOUR_TABLE_NAME

MS Access:
select Iif(max(YOUR_COLUMN_NAME) is null, 0,max(YOUR_COLUMN_NAME) + 1)
from YOUR_TABLE_NAME

SQL Server:
select ISNULL(max(YOUR_COLUMN_NAME)+1,1) from YOUR_TABLE_NAME

Last identity Value
SQL Server and MySQL:

select @@IDENTITY

For SQL Server GUID:

select newid()

Mandatory fields

For char fields:

select ‘always this text’

For integer fields

select 6

For current date

select GETDATE()

For advanced users:
Dynamic parameters defined in Filter rows can be used and will be replaced with runtime values.

See also:

Primary Key Storage

Assign table

Conversion

Clear primary key from Outlook items

Attachment parent table mappings


Filter rows

Configuration ›› Database ›› Assign table ››

Filter rows

It is possible to enter a SQL Where clause to limit the records in the result set.

Where clause can be a static SQL Statement (example: CountryCODE= ‘US’)

or dynamic parameter can be used.

Select the parameter in the combo box and click the Insert button, the parameter will be inserted in the edit field.
During the synchronization, GeniusConnect will replace the parameter tag with current system values:

{DB_LOGIN_USER} runtime value:
Login used to connect to the database (NT User if NT security used)

{DB_USER_NAME} runtime value:
Username returned by the database

{WINDOWS_USER} runtime value:
Current windows (NT) user

Example runtime value: John

{WINDOWS_NT_USER} runtime value:
Current windows (NT) user, (domain included)
Example runtime value: mydomain\John

{OUTLOOK_USER} runtime value:
Current Outlook user (only Outlook 2003 or higher)

{OUTLOOK_USER_EMAIL} runtime value:
Current Outlook user email (only Outlook 2003 or higher)

{OUTLOOK_FOLDER_FULL} runtime value:
Current Outlook folder full path and name
Example runtime value Outlook 2000: Calendar

Example runtime value Outlook 2002 or higher: \\Personal Folders\Calendar

{OUTLOOK_FOLDER_NAME} runtime value:
Current Outlook folder name
Example runtime value: Calendar (same us {OUTLOOK_FOLDER_FULL} in Outlook 2000)

{EXCHANGE_FOLDER_OWNER} runtime value:
Exchange only! Name of a Mailbox Folder Owner. (Not for Public Folders)
Example runtime: John Smith

{EXCHANGE_FOLDER_OWNER_ENTRYID} runtime value:
Exchange only! MAPI EntryID (unique identifier used by Outlook/Exchange) of a Mailbox Folder Owner. (Not for Public Folders)
Example runtime: 000000001A447390AA6611CD9BC800AA002FC45A03001C29EC2B445D7C4297…

{EXCHANGE_FOLDER_USER_NAME} runtime value:
Exchange only! Name of a Mailbox Folder user.
Example runtime: John Smith

{EXCHANGE_FOLDER_OWNER_EMAIL} runtime value:
Exchange only! email of Mailbox folder owner.
Example runtime: John@Smith.com

{COMPUTER_NAME} runtime value:
Current NetBIOS name of the local computer

Use the “Test Query” button to check if your statement is correct!

Ignore Filter for record Update

Check this option to ignore filter during the updates, if not checked the filter is applied also during the database updates.

Ignore Filter for record Delete

Check this option to ignore filter during the deletes, if not checked the filter is applied also during the database deletes.

It is recommended to use “view” in the database instead of GeniusConnect filters.

If your database supports views, please see support topic Using Views...

See also:

Assign fields

Configuration - Database

Conversion

Create a data source

Field information

Mandatory keys

Outlook Filter


Folder Options

Configuration ›› Database ›› Assign table ››

Folder Options

These options can be set per folder. The availability of these options depends on license type; please see www.geniusconnect.com for license details.

Only synchronize non-private items
Ignore Private Outlook Items.

Only insert and do not update Outlook items
Only add new items to Outlook. (Never update existing Outlook items)

Only insert and do not update Database items
Only add new items to the database. (Never update existing records)

Only update and do not insert Database items
Only update existing database records. (Never insert new records)
Use this option if Records may be created only using a specific application or process. GeniusConnect will ignore items created in Outlook, it will update only records previously loaded to Outlook from database.

Auto Save on Outlook Item change
Save Outlook Item(s) to database immediately after a change has been made. New Items will be automatically inserted.

Auto Load selected item(s) from database
Read database data of selected item(s) and updates Outlook Item(s). Only linked items will be refreshed, items with no primary key info (never loaded or stored) will be ignored. Max. 50 selected items will be refreshed.

Follow Outlook offline mode
Use this option if the database (mapped to the folder) is not located on your PC and you are working disconnected from network. If “Auto Save on Outlook Item change” or “Delete Database Record when Outlook Item deleted” options are enabled, the actions will be recorded and executed immediately after you switch back to online state. Scheduled synchronization (for the folder) and auto load (Auto Load selected item(s) from database) will be blocked in offline mode.
The manual synchronization using the toolbar buttons is not blocked.
Not supported for Outlook 2000, only Outlook 2002 or higher.

Delete all Outlook Items before Load All
All Outlook Item(s) will be deleted before Load All action. Use this option to empty the folder before loading all items. (Private Items will not be deleted if the “Only synchronize non-private items” option is used)

Delete Outlook Item when database record deleted (on load / load all)
Outlook Items that are not in de database anymore will be deleted. Items added to the folder manually (or never saved to the database) will be ignored.

Silent delete – if selected, Outlook Items will be deleted without any interaction with the user.

Delete all Database Records before Store All
All records will be deleted before Store All action. Use this option to empty the database table before storing all items.
(If Filter is used: the where clause will be used for the delete action)

Delete Database Record when Outlook Item deleted
Database record will be deleted immediately after Outlook Item has been deleted.

Silent delete – if selected, database records will be deleted without any interaction with the user.

Disable Toolbar buttons
checked items will be disabled. See also Customize Toolbar

Time Out in second’s
Time limit before a query to the database times out. (Enter 0 to wait infinite). This value overrules the Application Time Out setting!

See also:

Configuration - Database

Advanced Options

SQL Commands

Database change detection


Folder Advanced Options

Configuration ›› Database ›› Assign table ››

Folder Advanced Options

Use LastModificationTime

If you use this option, GeniusConnect will compare Outlook Item LastModificationTime value with database record modification time.
(a table column MUST be linked to LastModificationTime Outlook column!).

If a conflict occurs (Outlook Item and record LastModificationTime differs) you can choose:

  1. Ignore the Item
    No action
  2. Outlook data will overrule the database data
    regardless of current action (Load or Store) Outlook data will be saved to the database and will overwrite the database data
  3. Database data will overrule Outlook data
    regardless of current action (Load or Store) database data will be saved to Outlook and will overwrite Outlook Item data
  4. Most recent time wins
    Winner depends on the time value. The most recent value wins.

This behavior will apply for all sync directions (Load All, Load Item, Store All, Store Item).

Primary Key storage

Requirements for successful implementation of Modification time compare:

  1. A table column MUST be linked to the LastModificationTime Outlook column

  2. The table column data type must be able to store date-time values including seconds
  3. Time on all PC’s and servers involved in updating the table MUST be synchronized
  4. If the data is modified by applications, database processes etc. outside GeniusConnect, the database column MUST be updated with current
    (time of update) date time value. (See trigger example below)
  5. If the data is modified by GeniusConnect, the database column MUST contain value which has been provided by GeniusConnect. (your triggers may NOT overwrite the value with other date time value)

Note:

Outlook, or other application and Outlook processes can update Outlook Item values which will cause LastModificationTime update outside of GeniusConnect!

Compare only LastModificationTime

If checked, GeniusConnect will compare ONLY Outlook item and database record LastModificationTime values, to decide if there is a difference. Other columns will be ignored.
This option can greatly improve the performance!

All PC’s involved in the synchronization should use same date and time!

(Client PC, Exchange server, Database server)

To achieve best synchronization reliability for a folder:

  • Implement the LastModificationTime compare like described above
    Check: Compare only LastModificationTime checkbox
  • Use the “Auto Save on Outlook item change” option ( Folder Options )
  • Use the “Delete Outlook Item..” and “Delete database record” option (Folder Options)
  • Use the auto sync options and plan the “Load All” (or “Load All/Store All”)direction for the folder.
    OR
    Implement Database change detection .
    If you are using Database change detection mechanism, you can still use auto sync, but the frequency can be much lower. You can run auto sync with “Load All/Store All” for example only during night hours. This will ensure that also *out of date records get synchronized.

*It is possible that the Database change detection or Auto Save fails to synchronize if:
-Database server is down
-Network is down
-other reasons (record locked, timeout from database or Exchange etc…)

Example:

MS SQL Server Trigger example to auto update LastModificationTime.

This trigger is part of the MS SQL server example creation script.

CREATE TRIGGER tuAutoUpdateContacts ON [dbo].[CONTACTS]

FOR UPDATE not for replication

as

begin

if @@rowcount = 0

return

if not update(gc_LastModificationTime)

begin

update c

set gc_LastModificationTime = CURRENT_TIMESTAMP

from CONTACTS c inner join inserted i on (c.OCS_ID=i.OCS_ID)

end

end

Folder Options

SQL Commands

Database change detection


Primary Key Storage

Configuration ›› Database ›› Assign table ››

GeniusConnect stores information about record primary key value in Outlook item or in a file.

Outlook item storage (default setting):

GeniusConnect will create custom fields (MAPI user-defined fields) inside Outlook items to store database record primary key value.

Pro

less maintenance/no external files

Con

Performance decrease on MS Exchange

Possible problems on MS Exchange with Outlook cached mode

File storage:

GeniusConnect will store database record primary key value in a file outside Outlook.

Pro

Better performance on MS Exchange

No problems on MS Exchange with Outlook cached mode

Con

more maintenance/external files must be included in regular backup!

How To...

Assign Table->Options->Advanced->Primary Key storage

When changed for existing folder mapping, GeniusConnect will activate conversion automatically after closing Assign Table dialog.

Convert folders to another storage:

GeniusConnect->Database->Right Click the folder->Convert Primary key storage...

Change default storage(does not change existing folder configuration, only default values for new table assignments):

GeniusConnect->Database->Right Click any folder->Primary key storage defaults...


ANSI quoted identifiers

Configuration ›› Database ›› Assign table ››

ANSI quoted identifiers

SQL statements are generated using ANSI quoted identifiers.
Column/Table/View names with, for example space characters or reserved keywords, can be used.

EXAMPLE: The following SQL Statement will fail if executed without ANSI quoted identifiers.

Select My Column from My Table

If executed using ANSI quoted identifiers, the statement will be translated to:

Select “My Column” from “My Table”, and the execution will succeed.

IMPORTANT: if you are using old ODBC drivers, please upgrade to version that supports ANSI quoted identifiers!

If your database does not support ANSI quoted identifiers, or you are not using space characters, uncheck the checkbox.


User Defined Fields

Configuration ›› Database ›› Assign table ››

User Defined Fields

It is possible to define custom fields in Outlook. These fields can be linked to a table / view column.

The User Defined Field(s) must exist in the Outlook Folder before starting the mapping process.

GeniusConnect will scan the first 50 Item’s in the folder to collect the user defined fields.

You can change the max. Scan parameter in the registry (regedit.exe):
Scanning more items will affect the performance!

Path:HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: MaxItemsScan Type: REG_DWORD

Value: 50 (default)

Example Value: 100 (decimal)

If the folder contains too many items without user defined fields so the scan process will take too much time or user defined fields are not present:

  1. Move the items temporarily to a different folder.
  2. Create a dummy item with user-defined fields (also assign dummy values to the user-defined fields).
  3. Go to GeniusConnect setup->Database and complete the assign table process.
  4. Remove the dummy item
  5. Move the items from the temp. folder back to the original folder

To enable display of MAPI named fields, see Show MAPI Named columns in Advanced settings...


Custom SQL Commands

Configuration ›› Database ›› Assign table ››

SQL Commands

If you want to use a specific SQL Command for Insert, Update or Delete SQL statement, enter the SQL in the Insert, Update or Delete edit box.

If you click the “Insert parameters” button, GeniusConnect will insert all (used/linked) table columns with ‘@’ sign.
Format Parameter: ‘@<Col. Name>’

Example:

Column: tbl_zipcode

Parameter: ‘@tbl_zipcode’

Note:
SQL Command for delete supports only primary key field and dynamic parameters.
During delete, GeniusConnect can detect value of the primary key, but not of other Outlook item columns.

Dynamic parameters can be used because they depend on Folder(Folder name..), Outlook (Outlook user..) or PC (NT User..) values and not on individual item values.

If you click the “Insert dynamic parameters” button, GeniusConnect will insert all dynamic parameters GeniusConnect will replace the parameters with Outlook values during the execution. See Filter rows for more info about dynamic parameters.

IMPORTANT:

For INSERT/UPDATE/DELETE, ODBC driver returns number of rows affected by a SQL statement.

GeniusConnect checks this value! Number of rows affected must be 1, otherwise GeniusConnect will display a warning or an error.

Your stored procedure/SQL Statement used for Insert/Update/Delete must also return “number of rows affected=1” even if your stored procedure causes other/more/ table modifications.

Examples:

ODBC syntax to execute a stored procedure:
{call spMyName (‘@myColumn1’,…)}

SQL Server syntax to execute a stored procedure:
EXECUTE spMyName (‘@myColumn1’,…

MS SQL Server example files:

SQL script: Example MSSQL Server custom sp.sql

Template: Example MSSQL Server Mail custom sp.ini

For advanced users:
Dynamic parameters defined in Filter rows can be used and will be replaced with runtime values.

Attachments specific:

If you are using custom stored procedures to Insert and update Attachments, some older database systems may require blob / image parameter to be the LAST parameter.

Example:

Sp_attachment_update(‘@parentID’, ‘@Attch_nr’,……., ‘@blob_column’)

Delete stored procedure for attachments
GeniusConnect will always provide values for parent key(s) to the delete stored procedure. The Attachment Number parameter (mandatory for attachments) can be empty (NULL) when deleting all attachments of an Outlook item.

GeniusConnect will call the custom delete stored procedure in the following situations:

  • During store (All/Item)
    Attachments table contains more attachments for an parent record then Outlook item. During this call the attachment number parameter contains always a valid attachment number (0,1,X, never NULL) to be deleted from database table
    Example: User deletes an attachment from an Task
  • Option "Delete Database record when Outlook Item deleted" Is ON, and user deletes an Outlook Item
    During this call the attachment number parameter will be empty (NULL), only the parent key(s) parameter is valid. (GeniusConnect executes attachment delete before parent record delete)

Example Delete stored procedure:

CREATE PROCEDURE spDeleteAttachment(@parent_guid uniqueidentifier, @AttachNr int) AS

BEGIN

If @AttachNr is not NULL

BEGIN

delete from Attachments
where parent_guid=@parent_guid and Attachment_nr=@AttachNr

END

Else

BEGIN

delete from Attachments
where parent_guid=@parent_guid

END

END


Database change detection

Configuration ›› Database ›› Assign table ››

Database change detection

Use this dialog to enable automatic database change detection.

You must create a stored procedure or SQL Statement returning only changed records.

Enable detection

Must be checked to enable auto detection.

Execute every X seconds

This number determinates execution frequency (in background) of your SQL statement.

Advanced registry setting (Changing this value, can cause database deadlocks):

Set Max. Number of simultaneous SQL executions for DB Detection threads.

You MUST restart Outlook when changing this value!

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: MaxDBDetectExec

Type: REG_DWORD

Value: This value must be greater than or equal to one (1 decimal)

3=Default

Insert Parameters
Use this button to insert dynamic parameters. (See topic Filter rows for param. description)

Test…

Use this button to test your SQL / stored procedure.
GeniusConnect will replace dynamic parameters with runtime values and execute the statement.

Change detection SQL command

This command can be any SQL command or stored procedure returning data in the following format:

Transaction nr, Change Type, Primary key 1, Primary key X….

Transaction number

Data type: any
Column Name: any

Value: The value must be unique (no two transaction records may have same value)

Change Type
Data type: char, length 1
Column Name: any
Value: i, u, d or r
i=insert (a new record has been inserted)

u=update (a record has been updated)

d=delete (a records has been deleted)

r=refresh (refresh all database records, same as Load All from database)
(primary key columns will be ignored with ‘r’ transactions)
It can be faster to refresh all data then huge number of individual transactions.

Primary key
Data type: same as your main table (table linked to the Outlook folder)
Column Name: same as your main table

Value: The value must be unique (no two records may have same value)

If your main table uses 1 primary key, result must return 1 primary key.
If your main table uses X primary keys, result must return X primary keys.

Change processed SQL command (optional)
This command can be any SQL command or stored procedure accepting Transaction number as parameter. Tag value: {_DETECT_TRAN_NR}.

You can use also any other dynamic parameters.
GeniusConnect will replace the tag {_DETECT_TRAN_NR} with real transaction number before executing the SQL command.

You do not need to enter this SQL command if your detection command (previous field) has mechanism to mark processed transactions.

Detection steps

  1. GeniusConnect detection process executes your detection command
  2. Result(s) will be transferred to the main (Outlook) process and GeniusConnect will start processing
  3. GeniusConnect detection process executes your “Change processed SQL command”(if any) with transaction numbers of the successfully transferred transactions.
  4. GeniusConnect detection process waits X seconds (see Execute every X seconds) before starting again (step 1)

Note:
The transactions will not be processed or step 3 will not be executed if:

    • User aborts execution during processing
    • User exits Outlook during processing

Database change detection Examples

To implement change detection, you must implement:

  1. mechanism to collect data changes in your table
  2. implement a SQL command to return the results to GeniusConnect
  3. implement a mechanism to mark processed transactions

Implementation depends on:

  1. your data model
  2. your database system (SQL Server, Oracle etc..)
  3. number of processes changing your data
  4. number of Outlook Folders using the same table and detection mechanism

Example is based on MS SQL Server 2005 and Calendar example table. See Microsoft SQL Server

Example scenario

  • Multiple user calendar folders are mapped to the table Calendar
  • All users can add, change and delete calendar items
  • Outlook Folder (full path and name) column is mapped to Calendar column gc_OutlookFolder
  • CalendarSignal table receives data from triggers implemented on Calendar table
  • Outlook user changing the data (in Outlook) must not receive his own changes (from DB detection SQL command)
  • Detection SQL command marks processed transactions per Folder and host PC

Database change detection Example SQL Scripts

(example stores HOST_NAME() to gc_orgPcName column. To detect changes made from the same PC but from a different process(not GeniusConnect) you can use for example: HOST_NAME() +'-'+APP_NAME())

Signal table
Collect all changes in CalendarSignal table

CREATE TABLE [dbo].[CalendarSignal](

[TranNr] [int] IDENTITY(1,1) NOT NULL,

[TranType] [char](1) NOT NULL,

[gc_id] [uniqueidentifier] NULL,

[gc_orgFolder] [varchar](100) NULL,

[gc_orgPcName] [varchar](100) NULL,

[TranDateTime] [datetime] NULL CONSTRAINT [DF_CalendarSignal_TranDateTime] DEFAULT (getdate()),

CONSTRAINT [PK_CalendarSignalTable] PRIMARY KEY CLUSTERED

(

[TranNr] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Mark process Transaction in CalendarSignalDone table

CREATE TABLE [dbo].[CalendarSignalDone](

[LastTranNr] [int] NULL,

[gc_orgFolder] [varchar](100) NOT NULL,

[gc_orgPcName] [varchar](100) NOT NULL,

CONSTRAINT [PK_CalendarSignalDone_1] PRIMARY KEY CLUSTERED

(

[gc_orgFolder] ASC,

[gc_orgPcName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Triggers to insert the changes in CalendarSignal table

CREATE TRIGGER [dbo].[dt_DeleteSignal]

ON [dbo].[Calendar]

AFTER DELETE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--this is a delete, we do not want old transactions with insert or update

delete t1 from CalendarSignal t1, deleted t2 where t1.gc_id=t2.gc_id

-- Insert records to signal table

insert into CalendarSignal (TranType, gc_id, gc_orgFolder, gc_orgPcName) select 'd', gc_id, gc_OutlookFolder, HOST_NAME() from deleted

END

GO

CREATE TRIGGER [dbo].[it_InsertSignal]

ON [dbo].[Calendar]

AFTER INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert records to signal table

insert into CalendarSignal (TranType, gc_id, gc_orgFolder, gc_orgPcName) select 'i', gc_id, gc_OutlookFolder, HOST_NAME() from inserted

END

GO

CREATE TRIGGER [dbo].[ut_UpdateSignal]

ON [dbo].[Calendar]

AFTER UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert records to signal table

insert into CalendarSignal (TranType, gc_id, gc_orgFolder, gc_orgPcName) select 'u', gc_id, gc_OutlookFolder, HOST_NAME() from inserted

END

Stored procedure used in GeniusConnect to delete a record

CREATE PROCEDURE [dbo].[sp_DeleteCalendarItem]

-- Add the parameters for the stored procedure here

@gc_id uniqueidentifier,

@FolderName varchar(50)

AS

BEGIN

if EXISTS (select 1 from Calendar where gc_id=@gc_id AND gc_OutlookFolder=@FolderName)

BEGIN

delete from Calendar where gc_id=@gc_id

END

ELSE

BEGIN

begin tran tranDeleteCalenderItem

SET NOCOUNT ON;

--Update the FolderName to the deleting/current folder

update Calendar SET gc_OutlookFolder=@FolderName where gc_id=@gc_id

--delete it, it will now use correct foldername in the CalendarSignal Table

SET NOCOUNT OFF;

delete from Calendar where gc_id=@gc_id

if @@error <> 0

rollback tran tranDeleteCalenderItem

else

commit tran tranDeleteCalenderItem

END

END

Stored procedure used in GeniusConnect to return the changes

CREATE PROCEDURE [dbo].[sp_get_changes]

@FolderName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

SET NOCOUNT ON;

declare @nLastDone int

declare @nMaxTran int

-- Select last processed transaction for a folder and host name

select @nLastDone=LastTranNr from CalendarSignalDone where gc_orgFolder=@FolderName AND gc_orgPcName = HOST_NAME()

-- Select last not processed transaction (ignore transactions triggered from folder and host)

select @nMaxTran=max(TranNr) from CalendarSignal where (gc_orgFolder <> @FolderName OR gc_orgPcName <> HOST_NAME())

-- Insert or update Last processed Transaction nr. For Folder and host

if EXISTS (select 1 from CalendarSignalDone where gc_orgFolder=@FolderName AND gc_orgPcName = HOST_NAME() )

BEGIN

update CalendarSignalDone SET LastTranNr=@nMaxTran where gc_orgFolder=@FolderName AND gc_orgPcName = HOST_NAME()

END

else

BEGIN

insert into CalendarSignalDone (LastTranNr, gc_orgFolder, gc_orgPcName) VALUES(@nMaxTran, @FolderName, HOST_NAME())

END

-- We want to return result, set NOCOUNT off

SET NOCOUNT OFF;

-- return transactions between lastDone (not included) and nMaxTran (included), (ignore transactions triggered from folder and host)

select TranNr, TranType, gc_id from CalendarSignal

where @nMaxTran IS NOT NULL AND

(@nLastDone IS NULL or TranNr > @nLastDone)

AND

(TranNr <= @nMaxTran)

AND

(gc_orgFolder <> @FolderName OR gc_orgPcName <> HOST_NAME())

END

GeniusConnect implementation

Use delete stored procedure in GeniusConnect

SQL Command: EXECUTE sp_DeleteCalendarItem '@gc_id','{OUTLOOK_FOLDER_FULL}'

Use detect stored procedure in GeniusConnect

SQL Command: {call sp_get_changes('{OUTLOOK_FOLDER_FULL}')}


Start-End SQL commands

Configuration ›› Database ›› Assign table ››

Start and End SQL Commands

Enter SQL command or stored procedure ff you want to use a specific SQL Command for Start or End of Sync.

Start SQL command will be executed after connection has been established , but before any sync action, End SQL will be executed after

all sync action are finished.

End SQL Statement won't be executed on errors or user abort (Cancel button pressed )!

If you click the “Insert dynamic parameters” menu/button, GeniusConnect will insert all dynamic parameters. GeniusConnect will replace the parameters with Outlook values during the execution. See Filter rows for more info about dynamic parameters.

Note:
Dynamic parameters can be used because they depend on Folder(Folder name..), Outlook (Outlook user..) or PC (NT User..) values and not on individual item values.

Use the "Test..." button to check your SQL syntax, any dynamic parameter will be replaced with Outlook value and statement will be executed, you will see results if successful or error from your database.

Examples:

ODBC syntax to execute a stored procedure:
{call spMyStart ('{OUTLOOK_FOLDER_NAME}')}

SQL Server syntax to execute a stored procedure:
EXECUTE spMyEnd ('{OUTLOOK_FOLDER_NAME}')

For advanced users:
Dynamic parameters defined in Filter rows can be used and will be replaced with run-time values.

SQL Server Example for simple Start/End database log:

SQL Server Table script:

/****** Object: Table [dbo].[SyncLog] ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[SyncLog](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Folder] [nvarchar](max) NOT NULL,

[Type] [nchar](10) NOT NULL,

[TimeLog] [datetime] NOT NULL,

CONSTRAINT [PK_SyncLog] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[SyncLog] ADD CONSTRAINT [DF_SyncLog_TimeLog] DEFAULT (getdate()) FOR [TimeLog]

GO

Start SQL:

INSERT INTO [dbo].[SyncLog] ([Folder],[Type]) VALUES ( '{OUTLOOK_FOLDER_FULL}', 'Start' )

End SQL:

INSERT INTO [dbo].[SyncLog] ([Folder],[Type]) VALUES ( '{OUTLOOK_FOLDER_FULL}', 'Stopt' )


Attachments

Configuration ›› Database ›› Assign table ››

Item attachments

Please note:

Mail Folders can contain huge amount of attachment data!

Synchronizing attachments will affect performance!
Be sure you have enough space in your database to store attachments.
Be sure your network can handle attachment synchronization!

Every Outlook/Exchange Item (Contact, Task, Mail etc.) can contain 0 or more attachments. Attachment can be any type of file, image, OLE object, message or only a link reference. Contact Pictures (Outlook 2003 and higher) are also stored as attachments. HTML Body of an Email, Contact etc. can contain images or other objects, these objects are also stored as attachments in Outlook/Exchange.

To synchronize attachments your attachment table must:

  • Contain a column to store attachment number (zero based index of the attachment).
  • Contain a foreign key to the parent table (Table used on Folder Items Tab)
  • Contain a blob / image column to store the attachment data or you must set extraction directory.

It is not mandatory to map other Attachment columns, but Outlook may not be able to recognize attachments when loading attachments to Outlook!

Working of the Item Attachments dialog is almost identical to Assign table –Folder Items dialog, with the following differences:

Parent table mappings and primary keys (Assign Table – Folder Items dialog) must be specified before defining the attachment mappings.

Enable attachment support
Check this option to enable attachment synchronization

Reuse connection

If checked (default) GeniusConnect will reuse connection info used for parent table (Folder Items Tab). To use a different database connection, uncheck the checkbox.

Map the attachment columns to table columns and specify Attachment parent table mappings (Mandatory Keys button)

Extract attachments to a folder

Set a folder for attachments extraction.

Remove extracted files on attachment delete
If checked, file will be deleted on attachment delete

When using extraction:

  1. "Attachment full path and filename" column MUST BE LINKED TO A TABLE COLUMN! GeniusConnect will store full path and file name in DB column linked to "Attachment full path and filename".
    File name format: <parent primary key>_<attachment index>_<original attachment name>.<original file type>. Example: 100_0_myfilename.doc
  2. You do not have to link “Attachment binary” column

Contact pictures in Outlook 2003 and higher
Outlook is using fixed values to recognize Contact Picture between other “normal” attachments.

When importing from a file, you must provide Outlook with these values:

Attachment filename: must be empty

Attachment full path and filename: <file> Example: d:\foto\mypicture.jpg

Attachment long filename: ContactPicture.jpg (this is mandatory, Outlook always uses this fixed name in attachment table)

Attachment method:1

Attachment rendering position:-1

When importing directly from DB (blob column):

Attachment binary: the picture

Attachment filename: must be empty

Attachment long filename: ContactPicture.jpg

Attachment method:1

Attachment rendering position:-1

Available attachment columns:

Attachment binary
Contains binary attachment data

Attachment content id
Embedded HTML Body image content identification header (only used with *HTMLBody)

Attachment content location
Embedded HTML Body image content identification header (only used with *HTMLBody)

Attachment filename
Attachment base filename and extension, excluding path

Attachment full path and filename
Attachment fully qualified path and filename

Attachment long filename
Attachment long filename and extension, excluding path.

Attachment method
Constant representing the way the contents of an attachment can be accessed.
1=Attachment binary contains the attachment data by value
2=full path and filename contains a fully qualified path identifying the attachment to recipients with access to a common file server
3=full path and filename contains a fully qualified path identifying the attachment
4=Same as 3 but the messaging system never resolves the reference(link only)
5=Attachment binary is an embedded message
6=Attachment binary is an embedded OLE object

Attachment mime tag
Formatting information about a Multipurpose Internet Mail Extensions (MIME) attachment. The composition of the string is defined in the RFC 1521 document. The format is type/subtype, such as application/binary or text/plain

Attachment number
MUST BE LINKED TO A TABLE COLUMN! Contains a number that uniquely identifies the attachment within its parent message.

Attachment rendering position
Offset, in characters, to use in rendering an attachment within the main message text.
All values other than 1 indicate the position within *HTMLBody at which the attachment is to be rendered.

Attachment is a contact picture
Read-only flag

*You need to map these columns only if you are also linking “HTMLBody” column in Assign table – Folder Items dialog. If the plain-text version of the body (“Body” Column) is used, Outlook will ignore the HTML formatting info in the attachments.

Please note:
Values and formatting of attachments columns depends on:

Outlook version, Exchange version, other Messaging systems (messages/mail from other mail systems) etc.

Filter rows button
function is identical to Assign table – Folder Items dialog.

Options button
this button will activate the SQL Commands dialog. You can specify custom stored procedures for Insert/Update/Delete statements. If not used, GeniusConnect will generate default SQL Commands based on mappings.

Export / Import button function is identical to Assign table – Folder Items dialog.

Example SQL Server Attachments Table:

CREATE TABLE [dbo].[MailAttachment] (

[gc_parent_guid] [uniqueidentifier] NOT NULL ,

[gc_attach_number] [int] NOT NULL ,

[GC_ATTACH_FILENAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GC_ATTACH_LONG_FILENAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GC_ATTACH_METHOD] [int] NULL ,

[GC_ATTACH_MIME_TAG] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GC_RENDERING_POSITION] [int] NULL ,

[GC_ATTACH_CONTENT_LOCATION] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GC_ATTACH_BINARY] [image] NULL ,

[GC_ATTACH_CONTENT_ID] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GC_ATTACH_FULL_PATH_AND_NAME] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Attachment Examples

See also:

Attachment parent table mappings

Export / Import settings


Attachment parent table mappings

Configuration ›› Database ›› Assign table ››

Attachment parent table mappings

Parent Key mapping:

Primary keys of the parent table must be mapped to attachment column(s).

GeniusConnect uses this mapping to synchronize attachments of a specific item.

Example:

Parent Table “Calendar” has primary key Cal_ID

Attachmet Table “CalendarAttachment” has composite Primary key, Parent_Cal_ID (reference to parent record) and Attach_Nr.

Mapping: ParentKey:Cal_id Child key: Parent_Cal_ID

The Attach_nr column must be mapped to “Attachment Number” column in Item Attachments dialog .

SQL Statements for Mandatory Keys:

You have to specify other mandatory keys for attachments (second Grid),

only if:

-Column is Mandatory in Attachment table
-Column is not linked to an Outlook Attachment column
-Column has no default value for inserts (specified in database)

(Working of the second grid is explained in Primary and Mandatory keys help topic.

See also:
Item attachments


Initial Sync

Configuration ›› Database ›› Assign table ››

Initial Sync

You can use initial sync if you have assigned a database table to an Outlook folder and the folder contains items (Contacts, Tasks etc.) which you want to associate with existing database records.

Initial Sync can be activated from Database Tab (“Initial sync” button).

Use of this function is optional.

You do not have to use this function if:

  • Outlook folder is empty
  • Database table is empty
  • Outlook Items are not present in the database or can not be matched with existing records

Search columns

You have to select columns to match the database records.

The list of columns shows all Outlook Columns linked to the table columns.

You have to select at least 2 columns to perform the database search.

You have to investigate your table data to decide which columns will be used.

If you check the “Use wildcards” check box, GeniusConnect will use the “Like” operator to perform the search, otherwise the “=” operator will be used.

Example:

Selected columns: Email, First Name

Outlook Item data: example@test.nl, Jim

Database record: example@test.nl, Jimmy

Performing the search using wildcards 1 record will be found since Outlook value “Jim” has been compared with Jimmy using the LIKE operator.

Performing the search using the ‘=’ operator no records will be found since value “Jim” is not equal to “Jimmy”.

Start search

Click the “Start search” button to perform the search action using the selected columns.

The operation can be cancelled by pressing the Escape key.

If the Outlook folder contains Items already associated with a database record (using Load/Store buttons or previous Initial Sync operation), these items will be ignored.

Result list

The result list will show:

  • Outlook item description (depends on Folder type; subject for mail items, Name for Contact items etc.)
  • Number of records found
  • Database value of columns used for search

If only 1 candidate record has been found, the checkbox for the item is automatically marked.

If NO records has been found the checkbox can NOT be marked

If more than 1 record has been found, the checkbox is NOT marked and you have to select the correct candidate using the “View/Change details” button.

Note:

GeniusConnect will show max. 20 records. If the “Records found” shows more then 2-3 records, you have to reconsider your search strategy, and choose more or different columns to perform the search!

Set the check mark for every Outlook Item you want to associate with the database record.

If you want to see detail data or select another record for association, select the row and click the “View/Change details” button.

After selecting the Outlook Items for association, click the “Associate checked items” button.

GeniusConnect will associate the checked items and the rows will be removed from the result list.

See more for “View/Change details” in Initial Sync Detail

Important:

After performing the initial sync, the Outlook Items are ONLY associated with the database record, the data are not synchronized! You have to decide the direction to sync the data.

For Initial Sync on Folder level:

In Outlook select the Folder and:

  • Click Load All if database data overrules Outlook data
  • Click Store All if Outlook data overrules Database data

For Initial Sync on Item level:

In Outlook select the Outlook Item and:

  • Click Load Item if database data overrules Outlook data
  • Click Store Item if Outlook data overrules Database data


Unicode syntax

Configuration ›› Database ›› Assign table ››

(available only in Unicode version)

Use this dialog to specify special syntax when dealing with Unicode strings in older database versions (your database columns must support unicode charachters).

Examples Unicode syntax:

MS SQL Server:

Prefix: N

Postfix:

See your database documentation for more info.

More Info...


Initial Sync Detail

Configuration ›› Database ›› Assign table ››

Initial Sync Detail

Use this dialog to compare Outlook item data and database record data.

Records Found List

Database records (candidates) found during the search operation are displayed in this list.

If you want to see details data, select a row. The Record data detail list will be filled with detail data.

If you want to change database record for association please mark the checkbox.
(Mouse click on the checkbox or space bar on a selected row).

Outlook data detail

Selecting a row in this list will automatically select the same row in the Record data detail list.

Prev. /Next buttons

Use these buttons to navigate to the next / previous result row in the Initial Sync Dialog.

(Buttons will navigate only to Outlook Items with one or more candidates)

After viewing/changing records for association, return to the

Initial Sync dialog and use the “Associate checked items” to complete the Initial Sync.


Conversion Table

Configuration ›› Database ›› Assign table ›› Conversion ››

Conversion

Available conversion depends on the Outlook field type and the database field type. In the table below the Microsoft Outlook Types are listed on the horizontal axis and the SQL types on the vertical axis:

 | Bool | Long | VarChar | Date | Time | DateTime
Bit | + | + | - | - | - | -
Char | + | + | + | - | - | -
Bigint / Integer | + | + | + | - | - | +2
Tinyint / Smallint | + | + | + | - | - | -
Double / Decimal / Numeric | + | + | + | - | - | -
Real / Float | + | + | + | - | - | -
Varchar | + | + | + | +1 | +1 | +1
Longvarchar | + | + | + | +1 | +1 | +1
Timestamp / Type_timestamp / Type_date / Type_time / DateTime | - | - | +1 | + | + | +
Binary | - | - | - | - | - | +2
LongVarBinary / Varbinary | - | - | - | - | - | -

+: Conversion possible, it is not mandatory to specify conversion format.

1: The user must specify a format for the conversion.

2: possible if stored as number of seconds elapsed since midnight (00:00:00) January 1, 1970.

See also:

Assign table

Conversion - Date and time

Conversion - Bit and Long

X400 SMTP Conversion

Conversion string array

Mandatory keys


Conversion string array

Configuration ›› Database ›› Assign table ›› Conversion ››

Conversion string array

If Outlook data type is a string array, GeniusConnect will use a separator to convert the array elements to a string value. The default Separator is , (comma).

The String Value can be stored in a varchar table column.
When loading from database to Outlook, GeniusConnect will use the separator to convert the string value back to the array elements.

To use a different separator enter the separator in the edit field. (After clicking “Conversion..” button ).

Examples:

Outlook Array Values: Blue Black White

Default Separator: “,”

Result String: : Blue,Black,White

Default Separator: “$$”

Result String: : Blue$$Black$$White

Conversion


Bit and Long

Configuration ›› Database ›› Assign table ›› Conversion ››

Conversion - Bit and Long

When no conversion is necessary you can leave the database values empty. In this case the Outlook values will be placed in the database fields without conversion.

Eaxamples:

See also:

Conversion - Date and time


Date and time

Configuration ›› Database ›› Assign table ›› Conversion ››

Conversion - Date and time

Database values stored as UTC*

Check only if Database values stored in UTC time

Format

The following format can be used for the date / time conversion:

%b Abbreviated month name

%B Full month name

%d Day of month as decimal number (01-31)

%H Hour in 24-hour format (00-23)

%I Hour in 12-hour format (01-12)

%m Month as decimal number (01-12)

%M Minute as decimal number (00-59)

%p Current local’s A.M. /P.M. indicator for 12-hour clock

%S Second as decimal number (00-59)

%y Year without century, as decimal number (YY)

%Y Year with century, as decimal number (YYYY)

%TIME Number of seconds elapsed since midnight (00:00:00) January 1, 1970. Database values must be only digits. Please do not use other code when using this format.

%TIME_TO_LOCAL Same format as %TIME, containing the UTC*-based file time to be converted into a local (PC) time.
Uses the current settings for the time zone and daylight saving time.
(DO NOT USE TOGETHER WITH CHECKBOX Database values stored as UTC)

To remove a trailing zero value a ‘#’ can be used. For day of month as decimal number (01-31) this becomes %#d (1-31). The example in the dialog must show a valid date before the format can be applied.

* Universal Time Coordinate

See also:

Conversion - Bit and Long


X400 SMTP Conversion

Configuration ›› Database ›› Assign table ›› Conversion ››

X400 SMTP Conversion

MS Exchange only!

MS Exchange uses a different format to store email addresses.

Example : /o=EAC/ou=SGSINCLL/cn=Recipients/cn=Group Information Services/cn=

To convert this format to a SMTP address: select the column in Assign Table Dialog and click “Conversion” button.

Please Note:

-conversion will slow down synchronization
-conversion is not guaranteed
-conversion is one way only! To SMTP, not from SMTP to exchange format
-do not use addresses type column when using SMTP conversion!


Advanced settings

Advanced settings ››

Advanced registry settings

These are advanced settings! DO NOT USE UNLESS YOU UNDERSTAND!

The registry can be edited manually in Microsoft Windows by running regedit.exe in the Windows directory.

However, careless registry editing can cause irreversible damage!

Some of the advanced settings can be edited in “Advanced Admin options” dialog. (See button Advanced admin options... in Configuration options dialog )

or

To start Regedit.exe

1.

Click Start, and then click Run.

2.

Type Regedit, and then click OK.

Disable Folder scan
To disable scan (in Folder Tree: Configuration Dialog->Database tab->Folder Tree) of slow folders, for example remote exchange folders etc. (will also disable all subfolders):

Path:HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: BlockedFolders Type: String

Value: Folder Names. use , as separator

Example Value: Contracting,Public Folders

Scan only folders
Scan (in Folder Tree: Configuration Dialog->Database tab->Folder Tree) only specific folders

Path:HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: IncludeFoldersPath Type: String

Value: Folder Names. use , as separator

Example Value: Personal Folders\Contacts,Public Folders\Calendar

Use local cache file
useful if working with slow remote folders (hosted MS Exchange, slow network) with the following properties:
-Delete database record option (Folder options dialog) on
-Slow start of Outlook
-large number of Outlook items

The initial start of Outlook with local cache on will still be slow, the subsequent start will be faster.

Path: HKEY HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\

Key: CacheFile Type: string

Value: empty (no cache) or a full path and file name
Example Value: c:\temp\GCCache.gc

Outlook change delay
Delay before an Outlook change gets committed to the database

Path:HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: OutlookChangeDelay Type: REG_DWORD

Value: 0 = No Delay or Nr of seconds

Example: 5 (5 seconds delay)

OutlookStartDelay

delay GeniusConnect components start during Outlook start

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: OutlookStartDelay

Type: REG_DWORD

Value: 200(default) (decimal in milliseconds)

Modification Time compare tolerance
Tolerance for LastModificationTime compare

Use this feature only when you are not able to use time synchronization of your PC’s!!

All PC’s involved in the synchronization should use same date and time!

(Client PC, Exchange server, Database server)

Path:HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: MaxTimeDifferenceSeconds Type: REG_DWORD

Value: 0 = no Tolerance or Nr of seconds

Example: 2 (2 seconds Tolerance)

Use Forward Only cursor
Use only “Forward” cursor. (Do not use Dynamic, Static etc.. database cursor)
When using server side cursor, GeniusConnect is able to execute multiple SQL Statements and still can use only 1 connection, but some database systems can become very slow when using server side cursors (or with a specific SQL statements).

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions\

Key: ForceForwardOnly Type: REG_DWORD

Value: 0(Default) or 1(=Forward Only)

BlockSQLServerFastForward
SQL Server only: Do not use SQL Server “FastForward” cursor.
On some versions of MS SQL Server, FastForward cursor can be very slow(=Microsoft Bug) when using Views. (MS SQL Server FastForward cursor is a subtype of generic “Forward Only” cursor)

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions\

Key: BlockSQLServerFastForward Type: REG_DWORD

Value: 0(Default) or 1(=Block)

Ignore row(s) affected

Workaround for MS Access on Office 365 ODBC driver SQL_ERROR for "Rows affected"

Execute in another thread

Run all SQL statements in another thread

Show Outlook Read only columns
Values of these columns (EntryID, Calculated columns etc..) will be only stored in database, it is not possible to load these values from db to Outlook.

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings

Key: ShowReadonlyFields Type: REG_DWORD

Value: 0 or 1(=show, Default)

Show MAPI Named columns
Enable scanning for MAPI columns not published by Outlook. GeniusConnect will scan existing folder item for MAPI Named properties,

MAPI named columns will display "(MAPI Named)" , Example: LastIndexingAttemptTime(MAPI Named)

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings

Key: ShowMapiNamedFields Type: REG_DWORD

Value: 0(Default) or 1(=show)

Trace all SQL Statements to a file
The file has .trc extension and is located in your User temp folder, Example:

C:\Documents and Settings\<User>\Local Settings\Temp

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions\

Key: SQLTrace Type: REG_DWORD

Value: 0 or 1(=trace)

Debug
Show more detail info in Progress/Log. (use this setting to detect for example columns causing update)

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions\

Key: Debug Type: REG_DWORD

Value: 0(Default) or 1(=Debug)

Block inspector refresh
Do not refresh an inspector window (a detail dialog of a Contact, Task etc..).

Default behaviour: GeniusConnect will close inspector window, write the data and re-open the inspector.

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions\

Key: BlockInspectorRefresh Type: REG_DWORD

Value: 0(Default) or 1(=do not refresh inspector)

Block Store Check

Do not check if folder storage is available in current Outlook MAPI profile (Default=Unchecked).

Outlook Filter

Path: HKEY HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Folders\<Folder ID>\

Key: OutlookFilter Type: string

Examples:

[CompanyName] = 'XXX'

[Categories] = 'Personal' Or [Categories] = 'Business'

[Categories] = 'Personal' And [CompanyName] = 'GeniusConnect’

Delete Outlook item after Save (to database)

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Folders\<Folder Entry ID>

Key: DeleteAfterSave Type:REG_DWORD
Value:1 (decimal) or 0 (0=default)

Show System Tables
Show System Tables in Table/View Combo box

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings

Key: ShowSysTables Type:REG_DWORD
Value: 1 (decimal) or 0 (1=default)

Ignore Primary Key on Insert

Outlook inserts a new Item during Copy/Paste (or Move to Folder) operations, all values from original item are copied to the new item.

GeniusConnect removes primary key info from the new item by default. (To avoid linking multiple Outlook items to the same database record)

You can overrule this behaviour by setting IgnorePrimaryKeyOnInsert value to 0 (zero)

Path: HKEY HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Folders\<Folder ID>\

Key: IgnorePrimaryKeyOnInsert Type: REG_DWORD

Value:1(decimal) or 0 (1=default)

Please note:

Changing this parameter can cause duplicate items in Outlook!

BlockToolbarBitmapLoad

Do not load Toolbar bitmaps (only text buttons)

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: BlockToolbarBitmapLoad Type: REG_DWORD

Value: 0(decimal=default) or 1(=Block)

SubmitAfterInsert
When synchronizing a Mail Folder, GeniusConnect will submit the Mail item for "Sending", instead of saving it in the folder.

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Folders\<Folder EntryID>

Key: SubmitAfterInsert

Type: REG_DWORD

Value (decimal): 0(=default)or 1(=Submit for sending)

LogFilePerSync
create a new log file per sync instead of per day(default). See log directory

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: LogFilePerSync

Type: REG_DWORD

Value (decimal): 0(=default)or 1(=Create log file per sync)

FileMaker ODBC driver BUG

FileMaker ODBC driver returns incorrect values for SQL_COLUMN_DISPLAY_SIZE and column Precision. This bug leads to "Out of Memory" errors, mainly in 64bit FileMaker ODBC driver. We are unable to detect column sizes in FileMaker tables until FileMaker fixes these bugs.

As workaround GeniusConnect will use buffer length of 128 chars. You can change this value in registry (start regedit):

HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: DefaultDBVarcharLength

type: DWORD

Default value: 128 (decimal) buffer length

For Developers:

HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings

key:PublishCOMAddin

Default=0, Set to 1 to make COM interface visible in COMAddin office object for VBA or other Add-ins

Developers can call methods on GeniusConnect COM interface.

More: https://support.geniusconnect.com/Posts/t556-How-to-access-GeniusConnect-from-VBA


Outlook Filter

Advanced settings ››

Outlook Filter

Advanced users only!

On Database Tab, right click a Folder->Advanced :

How to create and test a filter in Outlook:

1.select the folder in Outlook, right click the column header->View settings->button "Filter"

2.Add fields with your criteria

3.Close Outlook Filter dialog

4.Test filter results in Outlook, repeat the steps to modify your filter

Copy the filter to GeniusConnect:

1.select the folder in Outlook, right click the column header->View settings->button "Filter"

2.Click the SQL Tab

3.Check "Edit these criteria directly..."

4.Copy the query

5.Open GeniusConnect config->Database Tab

6.Right Click the folder->Advanced->"Add/Edit Outlook Filter"

Enter: @SQL=here your query

Example:

@SQL="urn:schemas:calendar:dtstart" >= 'last month'

If you are not using DASL filter you can enter the filter directly without "@SQL=" keyword.

Examples:

[CompanyName] = 'XXX'

[Categories] = 'Personal' Or [Categories] = 'Business'

[Categories] = 'Personal' And [CompanyName] = 'GeniusConnect'

[Sensitivity] <> 'Confidential'

Today Outlook filter for Calendar items:

[Start] < tomorrow and [Start] > yesterday

Calendar filter "this and last month":

[End] >= lastmonth

DASL Examples:

To use DASL query, use:@SQL=<query>

Example 1:

End date <= today + 10 days (in seconds)

@SQL="urn:schemas:calendar:dtend" <= today(864000)

Example 2:

Start date >= last month

@SQL="urn:schemas:calendar:dtstart" >= 'last month'

Outlook Filter support topics...

Copyright © 2023 by GeniusConnect Team.


Manual sync using toolbar

Synchronize ››

Synchronize

When a folder is linked to a database table (and selected in Outlook) the toolbar shows 4 buttons for manual synchronization:

Load the selected item from the database if it is already stored in the database.

Load all records from the database table and insert or update the items in the selected folder.

Store the selected item in the database. If the item is not yet exported a new record is added to the table, otherwise the record is updated.

Store all items in the selected folder into the database table. Records are inserted or updated.

It is possible to start synchronization automatically (Business license and higher),
please see topic: Synchronize Schedule


Load item

Synchronize ››

Synchronize - Load item

Pressing this button will load the selected item(s) from the database and update the items in the Outlook folder. The items must have been exported to the database or previously imported from the database.

Loading items is only possible when updating is allowed (see Folder Options ).

See also:

Synchronize - Load all

Synchronize - Store item

Synchronize - Store all


Load all

Synchronize ››

Synchronize - Load all

Pressing this button will load all items available in the linked database table. New items will be added to the Outlook folder. Existing items will be updated only if this is allowed (see Folder Options ).

See also:

Synchronize - Load item

Synchronize - Store item

Synchronize - Store all


Store item

Synchronize ››

Synchronize - Store item

Pressing this button will store the selected items in the linked database table. New items will be added to the database. Existing items will be updated only if this is allowed (see Folder Options ).

See also:

Synchronize - Load item

Synchronize - Load all

Synchronize - Store all


Store all

Synchronize ››

Synchronize - Store all

Pressing this button will store all items in the selected Outlook folder in the linked database table. New items will be added to the database and the existing items will be updated only if this is allowed

(See Folder Options )

See also:

Synchronize - Load item

Synchronize - Load all

Synchronize - Store item


Progress

Synchronize ››

Progress

A progress bar will show the status of the synchronization. In the list view underneath the progress bar the success and failure of actions are shown. The error tab will show only errors and warnings. After synchronization has finished it is possible to close the dialog automatically (see Configuration - Options ). You can hide the status list view by clicking the “Details” button.

Bring to top
Bring the progress dialog to top and activate it during the synchronization

Do not show this dialog box during Load/Store
Disable the progress dialog. You can re-enable it in Configuration options or in Ribbon->Show Progress (Outlook 2010 or higher).

When an error occurs during the synchronization a popup dialog will show the error message. The progress dialog will not be closed.

See also:

Synchronize


Synchronize (auto)

Synchronize ››

Synchronize (auto)

Synchronize with database automatically

Interval

Choose when you want to synchronize

Synchronize only at start-up

Only at Outlook start-up

Synchronize every # minutes

Minimum of 1 minute and maximum of 1440 minutes (= 24 hours)

Extern Scheduler

Use the system scheduler. This can be any Scheduling system. To use default Windows scheduler: click the “Use Windows Scheduler” button.

To use another scheduling system:

Start the scheduler and add a new task.
Enter GCScheduler.exe as task to be executed.
The GCScheduler.exe is located in the GeniusConnect installation directory.
(Usually: “C:\Program Files\Genius@WORK\GeniusConnect”)

Use –s parameter to start Outlook automatically
Use –e parameter to exit Outlook automatically
Use –p <profile name> parameter to use a specific Outlook profile. (If your profile name contains a space, enclose the profile name in quotation marks.)

Use -n <scheduler/Task name> to use a specific scheduler/Task

(Only folders with <scheduler/Task name> specified on "Synchronize scheduler tab" will get synchronized), example Using Task Name for a specific Folder :

Use -o: Use specific Outlook start switch

See Microsoft documentation "Outlook Command-line switches" (URL depends on Outlook version, Google search: Outlook <2007, 2003 etc..> Command-line switches)

Advanced registry setting for GCScheduler.exe:

-WaitSecondsForOutlookStart

Specify GCScheduler wait time for Outlook (only used with –s [start Outlook])

HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings

KEY: WaitSecondsForOutlookStart

DWORD: default=15 (decimal)

Examples scheduler task command line:

GCScheduler.exe

Start Outlook
GCScheduler.exe -s

Start Outlook, exit Outlook after sync
GCScheduler.exe –s -e

Start Outlook with profile “myProfile”, exit Outlook after sync
GCScheduler.exe -p myProfile -s -e

Start Outlook, Exit Outlook , sync folders with TaskName TaskX

GCScheduler.exe -s -e -n TaskX

OR

Using Use Windows Scheduler Button:

Please see Log directory settings in Configuration - Options to enable log files.

Log files are created in the following format: scheduler<DATETIME>.log

Example: Scheduler211006151952.log

Windows scheduler example:

Please note:

“Run only if logged on” must be checked!
Outlook can NOT run in the background!

Direction

Load all database records into Outlook

Import records from the database

Store all Outlook items into the database

Export records to the database

Load all/Store all (import/export)

Database data will *overrule changes made in Outlook; new Outlook Items will be inserted in the database

Store all/Load all (export/import)

Outlook data will *overrule changes made in database, new Database records will be inserted into the Outlook folder.

None

No auto synchronization.

* If you are using “Last Modification Time” compare feature, the winner depends on settings in the Folder Advanced Options dialog. For best synchronization results use “Auto Save option” in Folder Options dialog and Load All as Direction for scheduled auto sync.

Folder sync. order

Select a folder and use up/down button to change synchronization order

Up

Move selected folder up

Down

Move selected folder down

See also: Database change detection


Customize the ribbon

Additional ››

Video: Customize the ribbon...


Customize Toolbar

Additional ››

Customize Toolbar

For Outlook 2010 or higher, please see Microsoft documentation Customize the ribbon...

Outlook 2000/2002/2003/2007 ONLY

The default Outlook support can be used to change display text, button position, etc.

To customize using Outlook:

  1. Right click GeniusConnect toolbar
  2. Click menu item “Customize…”

  3. To change a GeniusConnect button Right click the button

  4. To move the button to a different menu or toolbar, left click the button and drag it to the target location.

Please see Outlook documentation for more info.

To change the behaviour of a GeniusConnect button or remove it from the toolbar:

  1. Start regedit.exe
  2. Go to: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Commandbars\GeniusConnect

Buttons properties are stored in the following format:

ID

<ID>Caption (Can be changed using Outlook)

<ID>Show (Can be changed using Outlook-Delete)

<ID>CustomAction

Example:

The Toolbar position and visibility is stored in:

Position key, format is

Visible 0 or 1

X Left (distance from the left side of the docking area),

X Top (the distance (in points) from the top of the Office Assistant, or from the top edge of the specified command bar, to the top edge of the screen. For docked command bars, this property returns or sets the distance from the command bar to the top of the docking area),

X Toolbar Row Index (docking order of a command bar in relation to other command bars),

X Position

Please do not modify these values manually; you can also move/change the toolbar in Outlook.

To Hide/Show the Toolbar:

  1. Right click the Toolbar
  2. Uncheck the GeniusConnect item

To remove a button from the toolbar:

  • Set <ID>Show value to 0

To change the button function set the decimal value of <ID>CustomAction to:

  • 0, Load All records
  • 1, Store All records
  • 2, Load All and Store All
  • 3, Store All and Load All
  • 4, No action
  • 4294967295, default behaviour

To return to the default behaviour for the GeniusConnect Toolbar:

Delete HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Commandbars\GeniusConnect from the registry.


Export / Import settings

Additional ››

Export / Import settings

To Export / Import folder mappings and options to a template (.ini) use Export / Import buttons in Assign fields dialog (use same buttons also in Attachment Dialog ).

To Export / Import complete folder configuration including ODBC data source, table info, login and password, attachments etc. see Context menu info in Configuration - Database

Setup support topics...


Clear primary key from Outlook items

Additional ››

Clear primary key from Outlook items

GeniusConnect stores information about record primary key value in Outlook item or in a file (see Primary Key storage settings ) . If you have tested your configuration with for example wrong definition of primary keys or need to reset the sync and perform Initial sync , you may want to clear / delete the primary key data.

To Clear primary key data:

  • Go to Database tab
  • Right Click the folder and select “Clear primary keys” menu item
    or Hold the SHIFT keyboard key and double click the folder


Automatic Deployment

Additional ››

Automatic Deployment

If you are using tools like Microsoft System Center Configuration Manager, you can distribute the application and the application settings to different computers in your network.

Please note: this option is available only in Multi User license

MANDATORY Application components:

The Following application components must be copied and registered on the target PC:

regsvr32 GatWCrypto.dll

regsvr32 DBContact.dll

regsvr32 OutlookInterface.dll

regsvr32 OutlookConnect.dll

regsvr32 GeniusNTLog.dll

GCMMan.exe /RegServer

regsvr32 GCMManPS.dll

regsvr32 GeniusConnectSync.dll

regsvr32 secman.dll (secman64.dll for 64bit version)

The Following application components must be copied to the target PC:

Outlookconnect.chm

GCScheduler.exe

REMARK

GeniusConnect installs for ALL USERS by default:

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\Outlook\Addins\OutlookConnect.OutlookConnection.1

Settings:

To Distribute settings

1. Empty HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Folders\<Folder ID>Folder value

Folder(Entry) ID is unique identifier of an Outlook (Exchange) Folder. (GeniusConnect will scan only for folders with empty Folder value)

2. Set HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions RelinkFolders to 1

GeniusConnect (on the target PC)resets this value to 0 after the scan for local folders

3. Export registry key HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect

When the distribution is done and user starts Outlook, GeniusConnect will check the RelinkFolders key, if the value is 1, GeniusConnect will reset this key to 0 (to disable this process for the next start-up) and try to find (or recreate) the local folder using the FolderPath key.

If the Folder is not found, GeniusConnect will try to recreate the folder. After the Folder has been created (or existing folder has been found), the <ENTRY ID> key will be overwritten by Entry ID of the target account Folder.

Please note: the Scan process can take several minutes on slow networks in for example MS Exchange environment.

Export the ODBC data-sources (regedit):

You have to install ODBC drivers and distribute ODBC data source used by the Folder(s). GeniusConnect can't install 3rd party drivers and datasources.

System DSN is stored in the following locations:

DSN connection parameters:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<Your datasource name>

list of data sources

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

User DSN:

DSN connection parameters:

HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<Your datasource name>

list of data sources

HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

See also File DSN example:

Using ODBC File data source

https://www.geniusconnect.com/demosexamples/

Changing deployment folder path

The “FolderPath” Key contains original path of a Folder (only Folder Name for Outlook 2000).

HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Folders\<ENTRY ID>\FolderPath

If the folder path contains a user specific value, you can change it to a relative path.

Example 1-default calendar folder

Folder path: \\Mailbox - Administrator\Calendar

Relative path: \Calendar

Example 2-Calendar in a subfolder

Folder path: \\Mailbox - Administrator\Shared\Calendar

Relative path: \Shared\Calendar

GeniusConnect will search for relative path instead of exact folder match.

Please note: Outlook can contain multiple folders with the same name, use unique relative path\name combination.

Using folder path Wildcard

Use {*} wildcard in folder path:

You can use wildcards when distributing to an Outlook installation with multiple Mail Accounts. Relative path scan may link a wrong folder.

Example auto config Folder: TestContacts

Outlook accounts:

1. admin@company.com

2. local .pst file

Folder path: \\admin@company.com\TestContacts

Relative path: \TestContacts When using relative path GeniusConnect may link/create the folder in a wrong Outlook account

Wildcard path: \\{*}company.com\TestContacts

Block scanning of disabled stores/folders on target PC

Include HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions: BlockedFolders

Example:

Don't scan public folders on target PC during auto deployment

BlockedFolders: All Public Folders

Auto deployment support topics...

Copyright © 2023 by GeniusConnect Team.


Online Support and FAQ

Additional ››

Frequently asked questions

Please see support forum: https://support.geniusconnect.com/


How to access GeniusConnect from VBA

Additional ››

How to access GeniusConnect from Outlook Visual Basic for Applications (VBA)

1. Close Outlook

2. Start regedit and change PublishCOMAddin to 1

HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings

Key:PublishCOMAddin

Default=0, Set to 1 to make COM interface visible in COMAddin office object for VBA or other Add-ins

3.Start Outlook

Methods:

OnBarClick(int Button)

Button Param:

IDS_BUTTON_SETUP 33002 //Config dialog

IDS_BUTTON_STORE 33006 //Store selected item(s)

IDS_BUTTON_LOAD 33010//Load selected item(s)

IDS_BUTTON_LOADALL 33014//Load All (current folder)

IDS_BUTTON_STOREALL 33019//Store All (current folder)

IDS_BUTTON_THLP 33020//Start Help

IDS_BUTTON_MAILING 33024//Start Mailing

Example Code:

Public Sub StartConfigDialog()

Dim addIn As COMAddIn

Set addIn = Application.COMAddIns.Item("GeniusConnectSync.Connect")

Dim GC As Object

Set GC = addIn.Object

If Not (GC Is Nothing) Then

GC.OnBarClick 33002

End If

End Sub

SyncFolder(IDispatch* pFolder, int nDirection)

pFolder param:

MAPIFolder object

nDirection param:

0=Load All

1=Load selected

2=Save All

3=Save Selected

4=Load and Stora All

5=Store and Load All

Example Code:

Public Sub LoadAllContacts()

Dim addIn As COMAddIn

Set addIn = Application.COMAddIns.Item("GeniusConnectSync.Connect")

Dim GC As Object

Set GC = addIn.Object

Dim objFolder As Outlook.MAPIFolder

Set objFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderContacts)

If Not GC Is Nothing) Then

GC.SyncFolder objFolder, 0

End If

End Sub

Developers support topics...


32 bit ODBC on 64 bit Windows

Additional ››

When using 32-bit Outlook on 64-bit Windows, you must use 32-bit Microsoft ODBC Data source Administrator.

Default Windows path is : C:\Windows\SysWOW64\odbcad32.exe

Windows Vista, Windows 7, Windows 2008 64-bit:

Default Microsoft ODBC Data source Administrator In Administrative Tools (Control Panel->Administrative Tools) will create datasources with 64-bit ODBC drivers, these drivers are not compatible with 32-bit applications!

Windows 8 / Windows 10 64-bit:

In Administrative Tools on a 64-bit Windows 8 / 10 operating system, there are icons for both the 32-bit and 64-bit ODBC Data Source Administrator dialog box.

more info :

http://msdn.microsoft.com/en-us/library/ms712362

https://docs.microsoft.com/en-us/sql/odbc/admin/odbc-data-source-administrator


Microsoft SQL Server

Samples ››

Microsoft SQL Server

For use with Microsoft SQL Server there is a sample script to create the example tables.

Please run this script in MS SQL Server Query Analyzer to create the tables.

After table creation, please create a data source to the database and Name it

“GeniusConnect-MSSQLServer”.

Start Outlook and open the ‘GeniusConnect setup’ dialog.

Select on the database property page the Outlook folder you want to link to the database table.

Click the ‘Assign table’ button to open the ‘Assign table’ dialog.

In this dialog the data source must be selected first,

please select the “GeniusConnect-MSSQLServer” datasource.

Enter a login name and password (not necessary if you are using NT Security to connect with MS SQL Server) and click the ‘Connect’ button.

Now we have a connection to the database and a table can be selected.

When a table is selected a list with all available Outlook fields is displayed in the list view.

Import the example definition files, or link all fields manually.

It is advisable to use the definition files if you are new to GeniusConnect and databases.

To import the definition press the ‘Import’ button and select the definition file that matches your folder type. (Contact, Mail etc)

Files (usually: “C:\Program Files\Genius@WORK\GeniusConnect\SQLServer”)

-‘Example MSSQL Server - Outlook 2000 Contacts.ini’.

-‘Example MSSQL Server - Outlook Mail.ini’.

Etc...

Database creation script: Example MSSQL Server - Outlook.sql

Please follow the same steps for MySQL example, but use the MySQL example files.

MS SQL Server support topics...


Microsoft Access

Samples ››

Microsoft Access

For use with Microsoft Access there is a sample database.

During the installation an ODBC data source named, “GeniusConnect-Access”, has been created.

If you have installed the MS Access example files, this data source is configured to use the Microsoft Access example database.

To use another database Create a data source to the database.

Start Outlook and open the ‘GeniusConnect setup’ dialog.

Select on the database property page the Outlook folder you want to link to the database table. Click the ‘Assign table’ button to open the ‘Assign table’ dialog.

In this dialog the data source must be selected first (please select the “GeniusConnect-Access” data source for default example).

Enter a login name and password (not necessary for “GeniusConnect-Access” example) and click the ‘Connect’ button.

Now we have a connection to the database and a table can be selected.

When a table is selected a list with all available Outlook fields is displayed in the list view.

Import the example definition/template files, or link the fields manually.

It is advisable to use the definition files if you are new to GeniusConnect and databases.

To import the definition press the ‘Import’ button and select the definition file that matches your folder type. (Contact, Mail etc)

Files (usually: “C:\Program Files\Genius@WORK\GeniusConnect”)

‘Example MS Access - Outlook 2000 Contacts.ini’. (Can be used also for higher versions of outlook)

‘Example MS Access - Outlook Mail.ini’.
Etc…

Microsoft Access Example database:

ExampleAccess2000.mdb

Example manual linking:

Picture 1: Link the database field ‘OCS_FIRSTNAME’ to the Outlook field ‘First Name’ by selecting the database field.

MS Access support topics...


MySQL

Samples ››

MySQL

GeniusConnect installs (if selected during installation) one MySQL example:

(GeniusConnect install path\mySQL)

Example mapping template: “Example MySQL Outlook 2000 Contacts.ini”

Example Table: Contacts

(Run the database installation script before using this example: “Example MySQL – Outlook2000.sql”)

mySQL Support topics...


Attachment Examples

Samples ››

Attachment Examples

GeniusConnect installs (if selected during installation) two attachment examples:

MS SQL Server: (GeniusConnect install path\SQLServer)

Example mapping template: “Example MSSQLServer – Outlook Mail Attachment.ini”

Example Table: MailAttachment

(Run the database installation script before using this example:”Example MSSQLServer – Outlook.sql”)

MS Access: (GeniusConnect install path)

Example mapping template: “Example MS Access – Outlook Mail Attachment.ini”

Example Table: MailAttachment

(MS Access database file: ExampleAccess2000.mdb)

Both attachment example templates are working on Mail Folders linked to Mail example tables.

Please configure first the parent table mapping before importing the attachment examples.

“Example MSSQLServer – Outlook Mail.ini” or “Example MS Access - Outlook Mail.ini”

Examples installation details: Microsoft Access , Microsoft SQL Server


Ordering

Ordering ››

Ordering

Please see: https://www.geniusconnect.com/geniusconnect-order-online/

Licence options


Licence options

Ordering ››

Licence options

Please see: https://www.geniusconnect.com/geniusconnect-license-options/

Ordering


About and Contact Info

Ordering ››

About & Contact Info

Visit our website for more info: https://www.geniusconnect.com/contact/


Online samples and demos

 Redirecting... click here if nothing happens


Support

 Redirecting... click here if nothing happens